﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_ERP_Change_GetFAuditList]
	@PCId VARCHAR(250),
	@Stext VARCHAR(50),
	@StartDate VARCHAR(50),
	@EndDate VARCHAR(50),
	@State VARCHAR(4),
	@ProposeTitle VARCHAR(50),
	@FAuditPerson NVARCHAR(50),
	@StartIndex INT,
	@EndIndex INT
AS
BEGIN
	--处理查询条件
	DECLARE @SDate DATETIME
	DECLARE @EDate DATETIME
	SET @SDate = @StartDate + ' 00:00:00'
	SET @EDate = @EndDate + ' 23:59:59'
	DECLARE @swhere VARCHAR(4000)
	SET @swhere = ' AND edc.ApplionTime > ''' + CONVERT(VARCHAR(50),@SDate) + '''' + ' AND edc.ApplionTime < ''' + CONVERT(VARCHAR(50),@EDate) + ''''
	IF @PCId <> '0'
		BEGIN
			SET @swhere = @swhere + ' AND edc.PCompanyId IN (' + @PCId + ')'
		END
	SET @swhere = @swhere + ' AND (' + @State + ' = 0 OR edc.[State] =' + @State + ')'
	SET @swhere = @swhere + ' AND (''' + @Stext + ''' = '''' OR edc.ProposerName LIKE ''%' + @Stext + '%''' + ')'
	SET @swhere = @swhere + ' AND (' + @ProposeTitle + ' = 0 OR edc.ProposeTitle =''' + @ProposeTitle + ''')'
	
	--查询语句
	DECLARE @sql VARCHAR(4000)
	SET @sql = ' ;WITH List As(Select ROW_NUMBER() OVER (Order BY [State] DESC,CAuditTime Desc) AS Row,
		edc.id,
		ISNULL((select id from employee where username=edc.Proposer),0) as ProposerId,
		edc.Proposer,
		edc.ProposerName,
		CASE edc.ProposeTitle
			WHEN 90 THEN ''其它''
			WHEN 6 THEN ''资产管理''
			WHEN 5 THEN ''报销管理''
			WHEN 4 THEN ''成单变更''
			WHEN 3 THEN ''非企成单''
			WHEN 2 THEN ''非常规成单''
			WHEN 1 THEN ''常规成单''
		END AS ProposeTitle,
		edc.PCompanyId,
		(select CompanyName from Company where id=edc.PCompanyId) as PCompanyName,
		edc.PDepartmentId,
		(select DName from Department where id= edc.PDepartmentId) as PDName,
		edc.ApplionTime,
		edc.FAuditTime,
		edc.[State],
		CASE edc.[State]
			WHEN 5 THEN ''处理完毕''
			WHEN 4 THEN ''等待技术人员处理''
			WHEN 3 THEN ''等待财务审批''
			WHEN 2 THEN ''等待经理/主管审批''
			WHEN 1 THEN ''中止''
		END AS StateText
	FROM
		ERP_DataChange edc
	WHERE (edc.FAuditPerson = ''' + @FAuditPerson + ''')' + @swhere + '
	)'
	SET @sql = @sql + 'Select *,(Select Count(0) From list) As RecordCount From list Where Row Between ' + CONVERT(VARCHAR(50),@StartIndex) + ' and ' + CONVERT(VARCHAR(50),@EndIndex) + ' Order By Row'
	EXEC (@sql)
END

		--edc.BDetails,
		--edc.ADetails,
		--ISNULL((select id from employee where username=edc.CAuditPerson),0) as CAuditPId,
		--edc.CAuditPerson,
		--(select ename from employee where username=edc.CAuditPerson) as CAuditPersonName,
		--edc.CAudit,
		--edc.CMemo,
		--edc.CAuditTime,
		--ISNULL((select id from employee where username=edc.FAuditPerson),0) as FAuditPId,
		--edc.FAuditPerson,
		--(select ename from employee where username=edc.FAuditPerson) as FAuditPersonName,
		--edc.FAudit,
		--edc.FMemo,
